from flask import Blueprint, render_template, request, jsonify, abort
from flask_login import login_required, current_user
from user.role_check import get_all_cn
from utilities.cache_config import cache
from config import hue_hbase, mysql_database, mdl_dbid, hue_hive
from utilities.mysql_helper import getresult
from utilities.hive_helper import get_data_by_table
import random
import json
from collections import Counter

data_manage = Blueprint('data_manage', __name__)


@data_manage.route('/data_blood')
@login_required
def data_blood():
    return render_template('data_manage/data_blood.html', hue_hive=hue_hive, hue_hbase=hue_hbase)


@data_manage.route('/get_blood_data')
@login_required
def get_blood_data():
    @cache.cached(timeout=1800, key_prefix=''.join(request.args.values()))
    def getdata(args):
        sql = ''
        tablename = ''
        legend_data = []
        nodes = []
        nodes_dict = {}
        edges = []
        categories = []
        if 'tablename' in request.args:
            tablename = args['tablename']
            dbname = args['dbname']
            tablename = dbname + '.' + tablename
            sql = 'select * from jobhistory_datablood where input_table="%s" or output_table="%s" GROUP BY input_table,output_table' % (
                tablename, tablename)

        else:
            sql = 'select * from jobhistory_datablood  GROUP BY input_table,output_table'
        result = getresult(sql, (), mysql_database['portal'])
        if result:
            for row in result:
                flag = 0
                if 'cloudera_manager_metastore' not in row[1] and 'stg.' not in row[1] and 'default.' not in row[1] and '.' in row[1]:
                    if row[1] not in nodes:
                        nodes.append(row[1])
                        nodes_dict[row[1]] = 1
                    else:
                        nodes_dict[row[1]] = nodes_dict[row[1]] + 1
                    if '.' in row[1]:
                        legend_data.append('default')
                    else:
                        legend_data.append(row[1])
                    flag += 1
                if 'cloudera_manager_metastore' not in row[2] and 'stg.' not in row[2] and 'default.' not in row[2] and '.' in row[2]:
                    if row[2] not in nodes:
                        nodes.append(row[2])
                        nodes_dict[row[2]] = 1
                    else:
                        nodes_dict[row[2]] = nodes_dict[row[2]] + 1
                    if '.' in row[2]:
                        legend_data.append('default')
                    else:
                        legend_data.append(row[2])
                    flag += 1
                edges.append({'source': row[1], 'target': row[2]}) if flag == 2 else None
            nodes = [{'category': item[:item.index('.')], 'name': item, 'symbolSize': nodes_dict[item] * 5} for
                     item in sorted(nodes)]
            for item in sorted(nodes[:]):
                if '.' in item:
                    nodes.append({'category': item[:item.index('.')], 'name': item, 'symbolSize': nodes_dict[item] * 5})
                else:
                    nodes.append({'category': 'default', 'name': item, 'symbolSize': nodes_dict[item] * 5})
            legend_data = list(set(legend_data))
            categories = [{'name': item} for item in legend_data]
        else:
            nodes.append(tablename)
            legend_data.append(args['dbname'])
            categories.append({'name': args['dbname']})
        return (nodes, edges, legend_data, categories)

    result = getdata(args=request.args)
    return jsonify(nodes=result[0], edges=result[1], legend=result[2], categories=result[3])


@data_manage.route('/data_dict')
@login_required
def data_dict():
    return render_template('data_manage/data_dict.html')


@data_manage.route('/get_dict_db')
@login_required
def get_dict_db():
    @cache.cached(timeout=1800)
    def get_hive_db():
        sql = "select * from DBS where name not in ('default','stg') and name not like 'cloudera_manager_metastore%%' order BY NAME "
        result = getresult(sql, (), mysql_database["hive_metadata"])
        databases = {}
        for row in result:
            databases[row[0]] = row
        return databases

    databases1 = get_hive_db()
    return jsonify(data=databases1)


@data_manage.route('/get_dict_tbl')
@login_required
def get_dict_tbl():
    @cache.cached(timeout=1800)
    def get_hive_tbl():
        sql = "select a.*,b.PARAM_VALUE from TBLS as a LEFT JOIN TABLE_PARAMS as b on a.TBL_ID=b.TBL_ID and b.PARAM_KEY='comment' ORDER BY TBL_NAME"
        result = getresult(sql, (), mysql_database["hive_metadata"])
        tmp_dict = {}
        for row in result:
            if row[2] in tmp_dict:
                tmp_dict[row[2]][row[0]] = row
            else:
                tmp_dict[row[2]] = {row[0]: row}
        return tmp_dict

    tmp_dict1 = get_hive_tbl()
    return jsonify(data=tmp_dict1)


@data_manage.route('/get_dict_col')
@login_required
def get_dict_col():
    sdid = request.args['sdid']
    tblid = request.args['tblid']

    @cache.cached(timeout=1800, key_prefix=sdid + tblid + 'hivecol')
    def get_result(sdid, tblid):
        sql = "select * from (select a.*,'N' from COLUMNS_V2 as a ,SDS as b where b.CD_ID=a.CD_ID and b.SD_ID=%s order by a.integer_idx) as t1 UNION (select *,'Y' from PARTITION_KEYS where TBL_ID=%s ORDER BY integer_idx)" % (
            sdid, tblid)
        result = getresult(sql, (), mysql_database["hive_metadata"])
        tmp_list1 = []
        for row in result:
            tmp_list1.append(row)
        return tmp_list1

    tmp_list = get_result(sdid, tblid)
    return jsonify(data=tmp_list)


@data_manage.route('/data_mdl')
@login_required
def data_mdl():
    return render_template('data_manage/data_mdl.html')


@data_manage.route('/get_mdl_tbl')
@login_required
def get_mdl_tbl():
    @cache.cached(timeout=1800)
    def get_mdltbl():
        sql = "select a.*,b.PARAM_VALUE from TBLS as a LEFT JOIN TABLE_PARAMS as b on a.TBL_ID=b.TBL_ID and b.PARAM_KEY='comment' where a.db_id=%s ORDER BY TBL_NAME" % mdl_dbid
        result = getresult(sql, (), mysql_database["hive_metadata"])
        tmp_list = []
        for row in result:
            tmp_list.append(row)
        return tmp_list

    tmp_list1 = get_mdltbl()
    return jsonify(data=tmp_list1)


@data_manage.route('/get_mdl_detail')
@login_required
def get_mdl_detail():
    @cache.cached(timeout=1800, key_prefix=','.join(request.args.values()))
    def getdata(args, username):
        tablename = args['tablename']
        if 'cityid' in args:
            data = get_data_by_table(tablename, username, args['cityid'], args['pre7_phone'])
        else:
            data = get_data_by_table(tablename, username)
        return data

    data1 = getdata(request.args, current_user.get_name())
    return jsonify(data=data1)
